package com.echo.code.spring.jdbc;

import com.echo.code.spring.tx.User;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Description
 *
 * @author baiyp
 * @version 1.0.0
 * @date 2019/04/21
 */
public class JdbcClient {

  public static void main(String[] args) throws SQLException {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
    JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class);
    // update or delete
    int m = jdbcTemplate.update("update user set name = ? where id = ?", preparedStatement -> {
      preparedStatement.setString(1, "cc");
      preparedStatement.setInt(2, 7);
    });
    System.err.println(m);
    // insert and return primaryKey
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int n = jdbcTemplate.update(connection -> {
      PreparedStatement statement = connection.prepareStatement("insert into user(name, phone, address) values(?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
      statement.setString(1, "xx");
      statement.setString(2, "55555");
      statement.setString(3, "GZ");
      return statement;
    }, keyHolder);
    System.err.println(n);
    System.err.println(keyHolder.getKey());
    // batch

    // query
    List<User> list = new ArrayList<>();
    jdbcTemplate.query("select * from user", rs -> {
      User user = User.builder()
              .id(rs.getInt("id"))
              .name(rs.getString("name"))
              .phone(rs.getString("phone"))
              .address(rs.getString("address"))
              .build();
      list.add(user);
    });
    System.err.println(list);
    context.close();
  }
}
